package com.ndp.fb.rdb.db.impl;

import com.alibaba.dubbo.config.annotation.Service;
import com.ndp.fb.rdb.api.CustomAudienceTemplateRdbService;
import com.ndp.fb.rdb.db.DaoTemplate;
import com.ndp.fb.rdb.db.impl.mapper.DefaultRowMapper;
import com.ndp.fb.rdb.model.Pager;
import com.ndp.fb.rdb.model.customaudience.*;
import com.ndp.fb.util.ListUtil;
import com.ndp.fb.util.SQLUtil;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Created by elvisxiong on 15/12/8.
 */
@Service
public class CustomAudienceTemplateRdbServiceImpl extends DaoTemplate implements CustomAudienceTemplateRdbService {

    @Override
    public List<Long> batchSaveCustomAudienceTemplateAppRelation(Long templateId, List<Long> connectionObjectIdList) {
        List<CustomAudienceTemplateAndConnectionObject> list = new ArrayList<>();
        for (Long aLong : connectionObjectIdList) {
            CustomAudienceTemplateAndConnectionObject ct = new CustomAudienceTemplateAndConnectionObject();
            ct.setCustomAudienceTemplateId(templateId);
            ct.setConnectionObjectId(aLong);
            list.add(ct);
        }
        return batchSaveResultId(list);
    }

    @Override
    public List<CustomAudienceTemplateAndConnectionObject> getConnectionObjectIdByAudienceTemeplateId(Long templateId) {
       return  findByProperty("custom_audience_template_id",
                       templateId.toString(),DefaultRowMapper.autoRowMapper(CustomAudienceTemplateAndConnectionObject.class));
    }

    @Override
    public long save(final CustomAudienceTemplate template) {
        return saveResultId(template);
    }

    @Override
    public void update(CustomAudienceTemplate template) {
        String sql = "update custom_audience_template set name = ?, scale = ?,approximate_count = ?,user_id = ?,audience_type =?," +
                ",imported_type = ?,data_source_type = ?,data_source_subtype = ?,description = ?,subtype = ?,retention_days = ?," +
                "origin_id = ?,time_created = ?,time_updated= ?,time_content_updated = ?,is_delete = ?,origin_type=?,country_code=?," +
                "custom_ratio = ? where id =?";
        getJdbcTemplate().update(sql, ps -> {
            ps.setString(1, template.getName());
            ps.setString(2, template.getScale());
            ps.setLong(3, template.getApproximateCount());
            ps.setString(4, template.getUserId());
            ps.setString(5, template.getAudienceType());
            ps.setString(6,template.getImportedType());
            ps.setString(7, template.getDataSourceType());
            ps.setString(8, template.getDataSourceSubtype());
            ps.setString(9, template.getDescription());
            ps.setString(10, template.getSubtype());
            ps.setInt(11, template.getRetentionDays());
            ps.setLong(12, template.getOriginId());
            ps.setLong(13, template.getTimeCreated());
            ps.setLong(14, template.getTimeUpdated());
            ps.setLong(15, template.getTimeContentUpdated());
            ps.setInt(16, template.getIsDelete());
            ps.setObject(17, template.getOriginType());
            ps.setObject(18, template.getCountryCode());
            ps.setObject(19, template.getCustomRatio());
            ps.setLong(20,template.getId());
        });
    }


    @Override
    public Pager<?> findPagerByUserId(Long orgId,String audienceType,String importedType, int firstResult, int sizeNo) {
        Pager<CustomAudienceTemplateTable> pager = new Pager<CustomAudienceTemplateTable>();
        List<CustomAudienceTemplateTable> customAudienceTemplateList ;
        String countSql = "select count(1) cnt from custom_audience_template cat, user u where u.ticket = cat.user_id  and u.org_id = ? and audience_type = ? and imported_type = ? and  is_delete = 0 and subtype = 'CUSTOM'";
        String pagerSql = "select cat.id,cat.name,u.nickname as creator,cat.time_updated,cat.approximate_count from custom_audience_template cat,user u \n" +
                "where u.ticket = cat.user_id  and cat.imported_type = ? and cat.audience_type = ? AND u.org_id = ? AND cat.is_delete = 0 ORDER BY cat.time_created DESC LIMIT ?, ?";
        Object count = getJdbcTemplate().queryForMap(countSql.toString(), new Object[]{orgId,audienceType,importedType}).get("cnt");
        logger.debug("总数 >>>> " + count.toString());
        pager.setTotalSize(Long.parseLong(count.toString()));
        customAudienceTemplateList = getJdbcTemplate().query(pagerSql, new Object[]{importedType,audienceType,orgId,firstResult,sizeNo}, DefaultRowMapper.autoRowMapper(CustomAudienceTemplateTable.class));
        pager.setResultList(customAudienceTemplateList);
        return pager;
    }

    @Override
    public CustomAudienceTemplate findById(String templateId) {
        String sql = "select * from custom_audience_template where id = ? and is_delete = 0";
        return findOne(sql, new Object[]{templateId}, DefaultRowMapper.autoRowMapper(CustomAudienceTemplate.class));
    }

    @Override
    public void updateName(Long templateId, String name) {
        String sql = "update custom_audience_template set name = ? , time_updated= ? where id = ?";
        getJdbcTemplate().update(sql, new Object[]{name, new Date().getTime(), templateId});
    }

    @Override
    public void delByIds(List<Long> ids,String delReason)
    {
        String sql = "UPDATE custom_audience_template SET " +
                "is_delete = 1,del_reason = ? WHERE 1=1 AND " +
                "id = ?";
        getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setObject(1, delReason);
                ps.setObject(2, ids.get(i));
            }

            @Override
            public int getBatchSize() {
                return ids.size();
            }
        });
    }

    @Override
    public void updateApproximateCount(Long id, Long approximateCount)
    {
        String sql = "update custom_audience_template set approximate_count = ? where id = ?";
        getJdbcTemplate().update(sql, new Object[]{approximateCount, id});
    }

    @Override
    public void updateOperationTime(Long id)
    {
        String sql = "update custom_audience_template set time_updated = ? where id = ?";
        getJdbcTemplate().update(sql, new Object[]{new java.util.Date().getTime(), id});
    }

    @Override
    public List<CustomAudienceTemplateTable> getAllCustomAudienceTemplate(Long orgId, String audienceType, String importedType) {
        String sql = "select cat.id,cat.name,u.nickname as creator,cat.time_updated,cat.approximate_count from custom_audience_template cat,user u \n" +
                "where u.ticket = cat.user_id  and cat.imported_type = ? and cat.audience_type = ? AND u.org_id = ?  AND cat.is_delete = 0 ORDER BY cat.time_created desc ";
        return getJdbcTemplate().query(sql,new Object[]{importedType,audienceType,orgId},DefaultRowMapper.autoRowMapper(CustomAudienceTemplateTable.class));
    }

    @Override
    public List<CustomAudienceTemplate> findCustomAudienceTemplateByAccountId(Long accountId) {
        String sql = "SELECT DISTINCT cat.* FROM entrust_account_connection_objects eac, custom_audience_template_fb_connection_object cac, fb_userpermissions_adaccounts fua, custom_audience_template cat WHERE eac.fb_connection_object_id = cac.fb_connection_object_id AND eac.fb_account_id = fua.fb_account_id and cat.id = cac.custom_audience_template_id AND cat.is_delete = 0 AND fua.STATUS = 'ACTIVE' AND fua.fb_account_id = ?";
        return getJdbcTemplate().query(sql,new Object[]{accountId},DefaultRowMapper.autoRowMapper(CustomAudienceTemplate.class));
    }

    @Override
    public List<CustomAudienceTemplateAccountCount> findAllCustomAudienceTemplateIdAndAccountNum(String userId) {
       String sql = "SELECT custom_audience_template_id, COUNT(0) AS num\n" +
               "FROM custom_audience where is_delete = 0  and fb_account_id in(\n" +
               "select distinct fb_account_id from user u,fb_user fu,fb_userpermissions_adaccounts fua where u.ticket = fu.user_id and fua.fb_user_id = fu.fb_user_id and u.ticket = ? and fua.status ='ACTIVE'  AND fua.role IN ('ADMIN', 'GENERAL_USER') \n" +
               ")\n" +
               "GROUP BY custom_audience_template_id";
       return getJdbcTemplate().query(sql,new Object[]{userId},DefaultRowMapper.autoRowMapper(CustomAudienceTemplateAccountCount.class));
    }



    @Override
    public List<CustomAudienceTemplateAccountCount> findAllCustomAudienceTemplateIdAnAvaliableAccountNum(String userId) {
       String sql = "\tSELECT custom_audience_template_id, COUNT(0) AS num\n" +
               "\tFROM custom_audience\n" +
               "    WHERE is_delete = 0 and operation_status_code = '200'\n" +
               "    AND delivery_status_code = '200' and fb_account_id in(\n" +
               "select distinct fb_account_id from user u,fb_user fu,fb_userpermissions_adaccounts fua where u.ticket = fu.user_id and fua.fb_user_id = fu.fb_user_id and u.ticket = ? and fua.status ='ACTIVE' AND fua.role IN ('ADMIN', 'GENERAL_USER')\n" +
               ")\n" +
               " GROUP BY custom_audience_template_id";
        return getJdbcTemplate().query(sql,new Object[]{userId},DefaultRowMapper.autoRowMapper(CustomAudienceTemplateAccountCount.class));
    }

    @Override
    public List<CustomAudienceTemplateConnectionObjectName> findConnectionObjectNameByTempladteIds(List<Long> templateIdList) {
        String sql = "select catfco.custom_audience_template_id,co.name from custom_audience_template_fb_connection_object catfco,connection_object co where catfco.fb_connection_object_id = co.fb_connection_object_id and  custom_audience_template_id in("+ SQLUtil.appendPlaceholder(templateIdList.size()) +")";
        return getJdbcTemplate().query(sql,templateIdList.toArray(),DefaultRowMapper.autoRowMapper(CustomAudienceTemplateConnectionObjectName.class));
    }

    @Override
    public int getCustomAudienceItemCountByTemplateId(Long templateId) {
       String sql = "select count(0) from custom_audience where custom_audience_template_id  = ? and is_delete = 0";
        return getJdbcTemplate().queryForInt(sql, templateId);
    }

    @Override
    public List<CustomAudienceTemplateAndConnectionObject> getConnectionObjectByConnectionId(Long connectionObjectId) {
        String sql  = "select * from custom_audience_template_fb_connection_object where fb_connection_object_id = ?";
        return getJdbcTemplate().query(sql,new Object[]{connectionObjectId}, DefaultRowMapper.autoRowMapper(CustomAudienceTemplateAndConnectionObject.class));
    }

    @Override
    public void batchDelCustomAudienceTemplFbConnObjByTmplId(List<Long> tmplIds) {
        String sql = "UPDATE custom_audience_template_fb_connection_object SET " +
                "is_delete = 1 WHERE 1=1 AND " +
                "custom_audience_template_id = ?";
        getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setObject(1, tmplIds.get(i));
            }

            @Override
            public int getBatchSize() {
                return 0;
            }
        });
    }


    @Override
    public void deleteCustomAudienceTemplate(Long fbUserId,String delReason) {
        String sql = "UPDATE custom_audience_template SET is_delete = 1,del_reason =? WHERE 1=1  AND user_id = (select user_id from fb_user where fb_user_id = ?)";
        getJdbcTemplate().update(sql,new Object[]{delReason,fbUserId});
    }

    @Override
    public List<Long> getCustomAudienceTemplateId(Long fbUserId) {
        String sql = "select  id from custom_audience_template where user_id = (select user_id from fb_user where fb_user_id  = ?) and is_delete = 0";
        return getJdbcTemplate().queryForList(sql,Long.class,fbUserId);
    }

    @Override
    public List<CustomAudienceTemplate> findCanUseTmpl(Long orgId, String userId)
    {
        String sql = "SELECT\n" +
                "\tt.id, t.name " +
                "\tFROM\n" +
                "\tcustom_audience_template t,\n" +
                "\tuser u,\n" +
                "\tcustom_audience c\n" +
                "WHERE\n" +
                "\tt.user_id = u.ticket\n" +
                "AND t.id=c.custom_audience_template_id\n" +
                "AND u.org_id = ? \n" +
                "AND t.is_delete = 0\n" +
                "AND t.subtype = 'CUSTOM'\n" +
                "AND c.delivery_status_code = 200\n" +
                "AND c.operation_status_code = 200\n" +
                "AND c.request_queue_status = 1\n" +
                "AND c.is_delete = 0\n"+
                "AND c.fb_account_id IN (\n" +
                "\tSELECT\n" +
                "\t\tentrust_account.fb_account_id\n" +
                "\tFROM\n" +
                "\t\tentrust_account \n" +
                "\tINNER JOIN fb_userpermissions_adaccounts  ON fb_userpermissions_adaccounts.fb_account_id = entrust_account.fb_account_id\n" +
                "\tINNER JOIN fb_user ON fb_user.fb_user_id = fb_userpermissions_adaccounts.fb_user_id\n" +
                "\tWHERE\n" +
                "\t\tfb_user.user_id = ? \n" +
                "\tAND fb_userpermissions_adaccounts. STATUS = 'ACTIVE'\n" +
                ")\n" +
                "GROUP BY t.id,t.name";
        return getJdbcTemplate().query(sql, new Object[]{orgId, userId}, DefaultRowMapper.autoRowMapper(CustomAudienceTemplate.class));
    }

    @Override
    public CustomAudienceTemplate findCustomAudienceTemplateByoriginIdAndcountryCodeAndratio(Long originId, String countryCode, String ratio) {
        String sql = "SELECT * from custom_audience_template cu where cu.origin_id = ? and cu.country_code = ? and cu.custom_ratio = ? and cu.is_delete = 0";
        return findOne(sql,new Object[]{originId,countryCode,ratio},DefaultRowMapper.autoRowMapper(CustomAudienceTemplate.class));
    }

    @Override
    public Pager<CustomAudienceTemplateTable> findLookAlikePagerByUserIdAndName(Long orgId, String name, int firstResult, int sizeNo) {
        String sql;
        if(StringUtils.isNoneEmpty(name)){
            name = "%"+name+"%";
            sql = "SELECT cat.id,cat.name,u.nickname as creator,cat.time_updated,cat.approximate_count FROM custom_audience_template cat, `user` u WHERE u.ticket = cat.user_id AND u.org_id = ? and `name` like '"+name+"' AND is_delete = 0 AND subtype = 'LOOKALIKE' ORDER BY cat.time_created desc";
        } else {
            sql = "SELECT cat.id,cat.name,u.nickname as creator,cat.time_updated,cat.approximate_count FROM custom_audience_template cat, `user` u WHERE u.ticket = cat.user_id AND u.org_id = ? AND is_delete = 0 AND subtype = 'LOOKALIKE' ORDER BY cat.time_created desc";
        }
        return  findPager(sql,new Object[]{orgId},firstResult,sizeNo,DefaultRowMapper.autoRowMapper(CustomAudienceTemplateTable.class));
    }

    @Override
    public void batchDelCustomAudienceTemplFbConnObjByConnId(List<Long> connObjIds) {
        String sql = "select id from custom_audience_template_fb_connection_object where fb_connection_object_id IN (" + SQLUtil.appendPlaceholder(connObjIds.size()) + ")";
        List<Long> tmplIds = getJdbcTemplate().queryForList(sql, connObjIds.toArray(), Long.class);
        if(ListUtil.isEmpty(tmplIds))
            return;
        batchDelCustomAudienceTemplFbConnObjByTmplId(tmplIds);
    }

    @Override
    public List<Long> findHasLookALike(List<Long> ids) {
        String sql = "SELECT DISTINCT cu.origin_id FROM custom_audience_template cu WHERE cu.origin_id IN (" + SQLUtil.appendPlaceholder(ids.size()) + ") AND cu.is_delete = 0";
        return getJdbcTemplate().queryForList(sql,ids.toArray(),Long.class);
    }

    @Override
    public List<CustomAudienceTemplate> getCustomAudienceByOriginId(Long originId) {
        String sql = "select * from custom_audience_template where origin_id = ? and is_delete = 0 ";
        return getJdbcTemplate().query(sql,new Object[]{originId},DefaultRowMapper.autoRowMapper(CustomAudienceTemplate.class));
    }

    @Override
    public List<CustomAudienceTemplateTable> findAllLookAlikeByUserIdAndName(Long orgId, String name) {
        String sql;
        if(StringUtils.isNoneEmpty(name)){
            name = "%"+name+"%";
            sql = "SELECT cat.id,cat.name,u.nickname as creator,cat.time_updated,cat.approximate_count FROM custom_audience_template cat, `user` u WHERE u.ticket = cat.user_id AND u.org_id = ? and `name` like '"+name+"' AND is_delete = 0 AND subtype = 'LOOKALIKE' ORDER BY cat.time_created desc";
        } else {
            sql = "SELECT cat.id,cat.name,u.nickname as creator,cat.time_updated,cat.approximate_count FROM custom_audience_template cat, `user` u WHERE u.ticket = cat.user_id AND u.org_id = ? AND is_delete = 0 AND subtype = 'LOOKALIKE' ORDER BY cat.time_created desc";
        }
        return getJdbcTemplate().query(sql,new Object[]{orgId},DefaultRowMapper.autoRowMapper(CustomAudienceTemplateTable.class));
    }
}
